%Replication code for Table 1

% Table 1
% Summary statistics for credit card receivables portfolio characteristics
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% Table 1 presents summary statistics for the indicated characteristics of 
% the portfolios of credit card receivables underlying the securitizations. 
% The statistics for the individual card issuers are computed by taking averages 
% across all securitizations for each month, and then averaging the monthly
% averages. The monthly payment rate is the ratio of total cash flows collected 
% each month divided by the portfolio balance and is expressed as a
% percentage. Portfolio yield is the annualized percentage gross return on the 
% portfolio. Excess spread is the annualized percentage net return on
% the portfolio. The charge-off rate is the 1-month annualized percentage rate 
% of charge-offs on the portfolio. Risk-retention denotes the minimum
% percentage of portfolio receivables the issuer is required to hold. N denotes 
% the number of months. The sample period is monthly from January 2000
% to January 2020.

%Note: The statistics for the individual card issuers are computed by taking 
% averages across %all securitizations for each month, and then averaging 
% the monthly averages

clear; clc;

%Functions for summary stats
oMean = @(x) mean(x,1,'omitnan');
oMedian = @(x) median(x,1,'omitnan');
oStd = @(x) std(x,0,'omitnan');
oMin = @(x) min(x,[],'omitnan');
oMax = @(x) max(x,[],'omitnan');
oLQrt = @(x) prctile(x,25,1); 
oUQrt = @(x) prctile(x,75,1); 
oNObs = @(x) sum(~isnan(x),1); 

%A/B/C Tranche Triplets
opts = spreadsheetImportOptions("NumVariables", 27);
opts.Sheet = "AllCreditCards_Triplets_wPrem";
opts.DataRange = "A2:AA20121";
opts.VariableNames = ["RowNumber", "Year", "Month", "MPR", "PortfYld", "ChrgOff_1m", "ChrgOff_3m", "MasterTrustNo",...
    "CTrancheIndicator", "TrancheThickness", "NoMonthsExpMat", "NoMonthsFinalMat", "CpnA", "CpnB", "CpnC", ...
    "DiscA", "DiscB", "DiscC", "Prob", "ProbFitted", "ExSpread", "RskneutrSpread", "RskPrem",...
    "FltSpreadL3A","FltSpreadL3_B","FltSpreadL3_C","SellersInt"];
opts.VariableTypes = ["double", "double", "double", "double", "double", "double", "double", "double",...
    "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", ...
    "double", "double", "double", "double", "double","double", "double", "double", "double"];
CreditCardAbsDataset = readtable(".\Data_Table_01.xlsx", opts, "UseExcel", false);
clear opts
CreditCardAbsDataset.ExSpread = CreditCardAbsDataset.ExSpread.*100;
CreditCardAbsDataset.SellersInt = CreditCardAbsDataset.SellersInt.*100;
CreditCardAbsDataset = rmmissing(CreditCardAbsDataset);
CreditCardAbsDataset = standardizeMissing(CreditCardAbsDataset,-999);
CreditCardAbsDataset = CreditCardAbsDataset(CreditCardAbsDataset.Year>=2000,:);

%create issuer index
% 	1. Amex; 2. Bank of America; 3. Bank One; 4. Capital One; 5. Chase;
%   6. Citi; 7. Discover; 8. First National; 9. MBNA; 10. World Financial Network
issuerNames = {'Amex','BoA','BnkOne','Citi','Chase','CapOne','Disc','FrstNat','MBNA','WrldFin','Other'};
IssuerNo = NaN(size(CreditCardAbsDataset,1),1);
IssuerName = strings(size(CreditCardAbsDataset,1),1);
for idx=1:length(IssuerNo)
   currMasterTrustNo = CreditCardAbsDataset.MasterTrustNo(idx);
   if (currMasterTrustNo ==3)
       IssuerName(idx) = "Amex";
       IssuerNo(idx) = 1;
   elseif (currMasterTrustNo==4 || currMasterTrustNo ==5)
       IssuerName(idx) = "BoA";
       IssuerNo(idx) = 2;
   elseif (currMasterTrustNo==6 || currMasterTrustNo ==7)
       IssuerName(idx) = "BnkOne";
       IssuerNo(idx) = 3;
   elseif (currMasterTrustNo==8)
       IssuerName(idx) = "Citi";
       IssuerNo(idx) = 4;
   elseif (currMasterTrustNo==9 || currMasterTrustNo ==10)
       IssuerName(idx) = "Chase";
       IssuerNo(idx) = 5;
   elseif (currMasterTrustNo==11 || currMasterTrustNo ==12)
       IssuerName(idx) = "CapOne";
       IssuerNo(idx) = 6;
   elseif (currMasterTrustNo==13 || currMasterTrustNo ==14)
       IssuerName(idx) = "Disc";
       IssuerNo(idx) = 7;
   elseif (currMasterTrustNo==15)
       IssuerName(idx) = "FrstNat";
       IssuerNo(idx) = 8;
   elseif (currMasterTrustNo==16 || currMasterTrustNo ==17)
       IssuerName(idx) = "MBNA";
       IssuerNo(idx) = 9;
   elseif (currMasterTrustNo==19)
       IssuerName(idx) = "WrldFin";
       IssuerNo(idx) = 10;
   else 
       IssuerName(idx) = "Other";
       IssuerNo(idx) = 0;
   end
end
CreditCardAbsDataset.IssuerName = IssuerName;
CreditCardAbsDataset.IssuerNo = IssuerNo;
CreditCardAbsDataset = CreditCardAbsDataset(IssuerNo~=0,:);


% Compute Averages by year, month, issuer
CreditCardAbsDataset_m = grpstats(CreditCardAbsDataset,{'IssuerName','Year','Month'},...
    'mean','DataVars',{'MPR','PortfYld','ExSpread','ChrgOff_1m','SellersInt'},...
    'VarNames',{'Issuer','Year','Month','N','MPR','PortfolioYield','ExcessSpread','ChargeoffRate','RiskRetention'});

% Compute Averages by issuer
CreditCardAbsDataset_Issuer = grpstats(CreditCardAbsDataset_m,{'Issuer'},...
    'mean','DataVars',{'MPR','PortfolioYield','ExcessSpread','ChargeoffRate','RiskRetention'},...
    'VarNames',{'Issuer','N','MPR','PortfolioYield','ExcessSpread','ChargeoffRate','RiskRetention'});
CreditCardAbsDataset_Issuer = CreditCardAbsDataset_Issuer(:,...
    {'Issuer','MPR','PortfolioYield','ExcessSpread','ChargeoffRate','RiskRetention','N'});

% Compute Averages all
CreditCardAbsDataset_All = grpstats(CreditCardAbsDataset_m,[],...
    'mean','DataVars',{'MPR','PortfolioYield','ExcessSpread','ChargeoffRate','RiskRetention'},...
    'VarNames',{'N','MPR','PortfolioYield','ExcessSpread','ChargeoffRate','RiskRetention'});
CreditCardAbsDataset_All.Issuer = "All";
CreditCardAbsDataset_All = CreditCardAbsDataset_All(:,...
    {'Issuer','MPR','PortfolioYield','ExcessSpread','ChargeoffRate','RiskRetention','N'});
%Stack
Tab_1 = [CreditCardAbsDataset_Issuer; CreditCardAbsDataset_All];
Tab_1.Issuer=[];
  

%to cell
%format significant digits
tmpFun = @(x) sprintf('%3.3f',x);
tmpTbl_Cell = cellfun(tmpFun,num2cell(table2array(Tab_1)),...
    'UniformOutput',false);
tmpTbl_Cell = [ [{''};Tab_1.Properties.RowNames] ...
    [Tab_1.Properties.VariableNames; tmpTbl_Cell]];


%Output to command window
printtable(tmpTbl_Cell(2:end,2:end),'colVarNames', tmpTbl_Cell(1,2:end),...
    'rowVarNames', tmpTbl_Cell(2:end,1));


% +---------+--------+----------------+--------------+---------------+---------------+----------+
% |         |  MPR   | PortfolioYield | ExcessSpread | ChargeoffRate | RiskRetention |    N     |
% +---------+--------+----------------+--------------+---------------+---------------+----------+
% |  Amex   | 26.155 |     21.539     |    12.317    |     4.046     |     7.000     | 220.000  |
% |   BoA   | 15.246 |     19.540     |    7.839     |     7.039     |     4.000     | 143.000  |
% | BnkOne  | 19.056 |     17.498     |    7.537     |     5.642     |     4.162     | 160.000  |
% |  Citi   | 19.267 |     16.558     |    7.484     |     5.708     |     5.000     | 162.000  |
% |  Chase  | 19.654 |     16.831     |    7.630     |     5.051     |     4.000     | 185.000  |
% | CapOne  | 19.361 |     20.146     |    10.577    |     4.255     |     5.000     | 185.000  |
% |  Disc   | 19.580 |     17.719     |    8.145     |     5.536     |     7.000     | 159.000  |
% | FrstNat | 13.605 |     17.815     |    6.267     |     7.314     |     7.000     |  87.000  |
% |  MBNA   | 15.615 |     19.147     |    8.107     |     6.238     |     4.000     | 162.000  |
% | WrldFin | 17.649 |     30.237     |    15.338    |     7.178     |     4.602     | 221.000  |
% |   All   | 19.032 |     20.194     |    9.597     |     5.677     |     5.131     | 1684.000 |
% +---------+--------+----------------+--------------+---------------+---------------+----------+

